﻿using System;
using System.Collections.Generic;       
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Data.Sqlite;

namespace ConsoleApp.Sqlite
{                                 
    /// <summary>  
    /// SQLite数据库备份和恢复服务  
    /// </summary>  
    public class SqliteBackupService
    {
        // 数据库连接字符串  
        private readonly string _connectionString;

    // 数据库文件路径  
    private readonly string _databasePath;

    // 备份根目录  
    private readonly string _backupDirectory;

    // 备份保留策略（最大备份文件数）  
    private const int MAX_BACKUP_FILES = 10;

    /// <summary>  
    /// 构造函数  
    /// </summary>  
    /// <param name="dbPath">数据库文件路径</param>  
    /// <param name="backupDirectory">备份目录</param>  
    public SqliteBackupService(string dbPath, string backupDirectory)
    {
        _databasePath = dbPath;
        _connectionString = $"Data Source={dbPath};Version=3;";
        _backupDirectory = backupDirectory;

        // 确保备份目录存在  
        Directory.CreateDirectory(_backupDirectory);
    }

    /// <summary>  
    /// 执行手动备份  
    /// </summary>  
    /// <returns>备份文件路径</returns>  
    public string ManualBackup()
    {
        try
        {
            // 首先确保关闭所有数据库连接  
            using (var connection = new SqliteConnection(_connectionString))
            {
                connection.Open();
                // 执行VACUUM以优化数据库  
                using (var cmd = new SqliteCommand("VACUUM", connection))
                {
                    cmd.ExecuteNonQuery();
                }
                // 确保事务已提交  
                using (var cmd = new SqliteCommand("PRAGMA wal_checkpoint(FULL)", connection))
                {
                    cmd.ExecuteNonQuery();
                }
                connection.Close();
            }

            // 生成备份文件名（包含时间戳）  
            string backupFileName = $"backup_{DateTime.Now:yyyyMMdd_HHmmss}.db";
            string backupPath = Path.Combine(_backupDirectory, backupFileName);

            // 直接复制数据库文件  
            File.Copy(_databasePath, backupPath, true);

            // 如果存在WAL文件，也一并备份  
            string walPath = _databasePath + "-wal";
            if (File.Exists(walPath))
            {
                string walBackupPath = backupPath + "-wal";
                File.Copy(walPath, walBackupPath, true);
            }

            // 如果存在SHM文件，也一并备份  
            string shmPath = _databasePath + "-shm";
            if (File.Exists(shmPath))
            {
                string shmBackupPath = backupPath + "-shm";
                File.Copy(shmPath, shmBackupPath, true);
            }

            // 管理备份文件数量  
            ManageBackupFiles();

            LogInfo($"手动备份完成: {backupPath}");
            return backupPath;
        }
        catch (Exception ex)
        {
            // 记录错误日志  
            LogError($"备份失败：{ex.Message}");
            throw;
        }
    }

    /// <summary>  
    /// 定期自动备份（异步方法）  
    /// </summary>  
    /// <returns>异步任务</returns>  
    public async Task ScheduledBackupAsync()
    {
        try
        {
            // 在后台线程执行备份  
            await Task.Run(() => {
                ManualBackup();
            });
        }
        catch (Exception ex)
        {
            LogError($"定时备份失败：{ex.Message}");
        }
    }

    /// <summary>  
    /// 数据库恢复  
    /// </summary>  
    /// <param name="backupFilePath">备份文件路径</param>  
    public void RestoreDatabase(string backupFilePath)
    {
        try
        {
            // 验证备份文件是否存在  
            if (!File.Exists(backupFilePath))
            {
                throw new FileNotFoundException("备份文件不存在");
            }

            // 关闭所有数据库连接  
            GC.Collect();
            GC.WaitForPendingFinalizers();

            // 先创建一个当前数据库的备份，以防恢复失败  
            string tempBackupPath = Path.Combine(
                Path.GetDirectoryName(_databasePath),
                $"restore_backup_{DateTime.Now:yyyyMMdd_HHmmss}.db"
            );
            File.Copy(_databasePath, tempBackupPath, true);

            try
            {
                // 替换当前数据库文件  
                File.Copy(backupFilePath, _databasePath, true);

                // 恢复WAL和SHM文件（如果存在）  
                string walBackupPath = backupFilePath + "-wal";
                if (File.Exists(walBackupPath))
                {
                    File.Copy(walBackupPath, _databasePath + "-wal", true);
                }

                string shmBackupPath = backupFilePath + "-shm";
                if (File.Exists(shmBackupPath))
                {
                    File.Copy(shmBackupPath, _databasePath + "-shm", true);
                }

                LogInfo($"数据库已从 {backupFilePath} 恢复");
            }
            catch (Exception ex)
            {
                // 恢复失败，还原原始数据库  
                File.Copy(tempBackupPath, _databasePath, true);
                LogError($"恢复失败，已还原原始数据库: {ex.Message}");
                throw new Exception("数据库恢复失败，已自动回滚到原始状态", ex);
            }
            finally
            {
                // 清理临时备份  
                if (File.Exists(tempBackupPath))
                {
                    File.Delete(tempBackupPath);
                }
            }
        }
        catch (Exception ex)
        {
            LogError($"数据库恢复失败：{ex.Message}");
            throw;
        }
    }

    /// <summary>  
    /// 验证数据库备份的完整性  
    /// </summary>  
    /// <param name="backupFilePath">备份文件路径</param>  
    /// <returns>备份是否有效</returns>  
    public bool ValidateBackup(string backupFilePath)
    {
        if (!File.Exists(backupFilePath))
        {
            return false;
        }

        try
        {
            // 尝试打开备份数据库连接验证其完整性  
            using (var connection = new SqliteConnection($"Data Source={backupFilePath};Version=3;"))
            {
                connection.Open();

                // 执行简单查询测试  
                using (var cmd = new SqliteCommand("PRAGMA integrity_check", connection))
                {
                    string result = cmd.ExecuteScalar()?.ToString();
                    return result == "ok";
                }
            }
        }
        catch
        {
            return false;
        }
    }

    /// <summary>  
    /// 管理备份文件数量  
    /// </summary>  
    private void ManageBackupFiles()
    {
        try
        {
            // 获取所有备份文件  
            var backupFiles = Directory.GetFiles(_backupDirectory, "backup_*.db");

            // 按创建时间排序  
            Array.Sort(backupFiles, (a, b) =>
                File.GetCreationTime(a).CompareTo(File.GetCreationTime(b)));

            // 如果备份文件超过最大限制，删除最旧的备份  
            if (backupFiles.Length > MAX_BACKUP_FILES)
            {
                for (int i = 0; i < backupFiles.Length - MAX_BACKUP_FILES; i++)
                {
                    File.Delete(backupFiles[i]);

                    // 同时删除关联的WAL和SHM文件  
                    string walFile = backupFiles[i] + "-wal";
                    if (File.Exists(walFile))
                    {
                        File.Delete(walFile);
                    }

                    string shmFile = backupFiles[i] + "-shm";
                    if (File.Exists(shmFile))
                    {
                        File.Delete(shmFile);
                    }

                    LogInfo($"已删除过期备份: {backupFiles[i]}");
                }
            }
        }
        catch (Exception ex)
        {
            LogError($"备份文件管理失败：{ex.Message}");
        }
    }

    /// <summary>  
    /// 获取所有备份文件信息  
    /// </summary>  
    /// <returns>备份文件信息列表</returns>  
    public List<BackupInfo> GetBackupList()
    {
        var result = new List<BackupInfo>();
        var backupFiles = Directory.GetFiles(_backupDirectory, "backup_*.db");

        foreach (var file in backupFiles)
        {
            var fileInfo = new FileInfo(file);

            result.Add(new BackupInfo
            {
                FilePath = file,
                FileName = Path.GetFileName(file),
                CreationTime = fileInfo.CreationTime,
                Size = fileInfo.Length,
                IsValid = ValidateBackup(file)
            });
        }

        // 按创建时间降序排序（最新的在前）  
        result.Sort((a, b) => b.CreationTime.CompareTo(a.CreationTime));

        return result;
    }

    /// <summary>  
    /// 获取备份文件的详细信息  
    /// </summary>  
    /// <param name="backupFilePath">备份文件路径</param>  
    /// <returns>备份详细信息</returns>  
    public BackupInfo GetBackupDetails(string backupFilePath)
    {
        if (!File.Exists(backupFilePath))
        {
            return null;
        }

        var fileInfo = new FileInfo(backupFilePath);

        return new BackupInfo
            {
                FilePath = backupFilePath,
                FileName = Path.GetFileName(backupFilePath),
                CreationTime = fileInfo.CreationTime,
                Size = fileInfo.Length,
                IsValid = ValidateBackup(backupFilePath)
            }
        ;
    }

    /// <summary>  
    /// 日志信息记录  
    /// </summary>  
    /// <param name="message">日志消息</param>  
    private void LogInfo(string message)
    {
        // 实际项目中可替换为更完善的日志系统  
        Console.WriteLine($"[{DateTime.Now}] [INFO] {message}");
    }

    /// <summary>  
    /// 错误日志记录  
    /// </summary>  
    /// <param name="errorMessage">错误消息</param>  
    private void LogError(string errorMessage)
    {
        // 实际项目中可替换为更完善的日志系统  
        Console.Error.WriteLine($"[{DateTime.Now}] [ERROR] {errorMessage}");
    }
}

    /// <summary>  
    /// 备份文件信息类  
    /// </summary>  
    public class BackupInfo
    {
        /// <summary>  
        /// 备份文件完整路径  
        /// </summary>  
        public string FilePath { get; set; }

        /// <summary>  
        /// 备份文件名  
        /// </summary>  
        public string FileName { get; set; }

        /// <summary>  
        /// 备份创建时间  
        /// </summary>  
        public DateTime CreationTime { get; set; }

        /// <summary>  
        /// 备份文件大小（字节）  
        /// </summary>  
        public long Size { get; set; }

        /// <summary>  
        /// 备份是否有效  
        /// </summary>  
        public bool IsValid { get; set; }
    }
}